﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;


namespace IHISCETeim.Web.Export.FXModel
{
    public partial class ReportGetRZFXBP : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "经销商融资分析.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "经销商融资分析(汇总)";
            }
        }
        protected string LeaseCompany
        {
            get
            {
                return GetRequestValue<string>("LeaseCompany", "");
            }
        }
        protected DataTableCollection dts = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            //
            DataTableCollection dts = null;
            DataTable aNList = FXFinanceLeaseController.GetAliasNameLastYear(LeaseCompany);
           
            //            
           
          //  DataTable exportDT = dts[0];

            InitializeWorkbook();
           // hssfworkbook.CloneSheet(0);

           // hssfworkbook.sheet
            ///第一个工作表为　汇总表
            dts = FXFinanceLeaseController.ReportGetRZFXBP("", LeaseCompany);
            ISheet sheet0 = hssfworkbook.GetSheetAt(0);
           // hssfworkbook.SetSheetName(0, AliasName);
            string temptitle = string.Format("经销商融资{0}分析({1}汇总)", string.IsNullOrEmpty(LeaseCompany) ? "" : "(" + LeaseCompany + ")", "" + DateTime.Now.ToString("yyyy年MM月") + "");
            WriteSheet(sheet0, dts, temptitle);
            hssfworkbook.SetSheetName(0, temptitle);
            //标题
            sheet0.GetRow(0).GetCell(0).SetCellValue(temptitle);
            sheet0.ForceFormulaRecalculation = true;
            ///第二个工作表为　明细汇总表
            ISheet sheet1 = hssfworkbook.GetSheetAt(1);
            // hssfworkbook.SetSheetName(0, AliasName);
            temptitle = string.Format("经销商融资{0}分析({1}明细汇总)", string.IsNullOrEmpty(LeaseCompany) ? "" : "(" + LeaseCompany + ")", "" + DateTime.Now.ToString("yyyy年MM月") + "");
            WriteDetailSheet(sheet1, aNList);
            hssfworkbook.SetSheetName(1, temptitle);
            //标题
            sheet1.GetRow(0).GetCell(0).SetCellValue(temptitle);
            sheet1.ForceFormulaRecalculation = true;
            ////
            string AliasName = "";
            int sheetindex = 2;//从第二个工作表开始
            foreach (DataRow dr in aNList.Rows)
            {  
                AliasName = dr["AliasName"].ToString();
                dts = FXFinanceLeaseController.ReportGetRZFXBP(AliasName, LeaseCompany);
                ISheet sheet2 = hssfworkbook.CloneSheet(0);
                hssfworkbook.SetSheetName(sheetindex, AliasName);               
                //标题
                sheet2.GetRow(0).GetCell(0).SetCellValue(AliasName + "[" + DateTime.Now.ToString("yyyy年MM月") + "]");

                WriteSheet(sheet2, dts, AliasName);
                sheet2.ForceFormulaRecalculation = true;
                sheetindex++;
            }
            
            WriteToFile();
        }

      

        private void WriteSheet(ISheet sheet1, DataTableCollection dts,string title)
        {
            DataRow dt1 = dts[0].Rows[0];
            DataRow dt2 = dts[1].Rows[0];
            
            //
            sheet1.GetRow(1).GetCell(1).SetCellValue(Convert.ToInt32(dt1["总客户数"]));            
            sheet1.GetRow(2).GetCell(1).SetCellValue(Convert.ToDouble(dt1["剩余租金"]));
           // sheet1.GetRow(3).GetCell(1).SetCellValue(GetPer(dt1["逾期总额"], dt1["剩余租金"]));

            sheet1.GetRow(6).GetCell(1).SetCellValue(Convert.ToInt32( dt1["无逾期客户数"]));
            sheet1.GetRow(6).GetCell(3).SetCellValue(Convert.ToDouble(dt1["无逾期客户剩余租金"]));
            sheet1.GetRow(7).GetCell(1).SetCellValue(Convert.ToInt32(dt1["逾期1期客户数"]));
            sheet1.GetRow(7).GetCell(3).SetCellValue(Convert.ToDouble(dt1["逾期1期客户剩余租金"]));
            sheet1.GetRow(8).GetCell(1).SetCellValue(Convert.ToInt32(dt1["逾期2期客户数"]));
            sheet1.GetRow(8).GetCell(3).SetCellValue(Convert.ToDouble(dt1["逾期2期客户剩余租金"]));
            sheet1.GetRow(9).GetCell(1).SetCellValue(Convert.ToInt32(dt1["逾期3期及以上客户数"]));
            sheet1.GetRow(9).GetCell(3).SetCellValue(Convert.ToDouble(dt1["逾期3期及以上客户剩余租金"]));
            //
            sheet1.GetRow(12).GetCell(1).SetCellValue(Convert.ToInt32(dt2["总客户数"]));
            sheet1.GetRow(13).GetCell(1).SetCellValue(Convert.ToDouble(dt2["剩余租金"]));
            //sheet1.GetRow(14).GetCell(1).SetCellValue(GetPer(dt2["逾期总额"], dt2["剩余租金"]));

            sheet1.GetRow(17).GetCell(1).SetCellValue(Convert.ToInt32(dt2["无逾期客户数"]));
            sheet1.GetRow(17).GetCell(3).SetCellValue(Convert.ToDouble(dt2["无逾期客户剩余租金"]));
            sheet1.GetRow(18).GetCell(1).SetCellValue(Convert.ToInt32(dt2["逾期1期客户数"]));
            sheet1.GetRow(18).GetCell(3).SetCellValue(Convert.ToDouble(dt2["逾期1期客户剩余租金"]));
            sheet1.GetRow(19).GetCell(1).SetCellValue(Convert.ToInt32(dt2["逾期2期客户数"]));
            sheet1.GetRow(19).GetCell(3).SetCellValue(Convert.ToDouble(dt2["逾期2期客户剩余租金"]));
            sheet1.GetRow(20).GetCell(1).SetCellValue(Convert.ToInt32(dt2["逾期3期及以上客户数"]));
            sheet1.GetRow(20).GetCell(3).SetCellValue(Convert.ToDouble(dt2["逾期3期及以上客户剩余租金"]));

            //sheet1.GetRow(1).GetCell(1).SetCellValue(dt2["总客户数"].ToString());
        }

        protected Double GetPer(object 逾期总金额, object 剩余总租金)
        {
            //逾期总金额 /剩余总租金

            if (Convert.ToDecimal(逾期总金额) == 0)
                return 0;

            return Convert.ToDouble(逾期总金额) / Convert.ToDouble(剩余总租金);

        }

        /// <summary>
        /// 写入明细汇总表
        /// </summary>
        /// <param name="sheet1"></param>
        /// <param name="dts"></param>
        private void WriteDetailSheet(ISheet sheet1, DataTable AliasNameDT)
        {
            int startRow = 2;//开始写入行
            foreach (DataRow dr in AliasNameDT.Rows)
            {
                string AliasName = dr["AliasName"].ToString();
                dts = FXFinanceLeaseController.ReportGetRZFXBP(AliasName, LeaseCompany);
                DataRow dt1 = dts[0].Rows[0];
                DataRow dt2 = dts[1].Rows[0];

                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(startRow);
                row1.CreateCell(0).SetCellValue(AliasName);
                row1.CreateCell(1).SetCellValue(Convert.ToInt32(dt1["总客户数"]));
                row1.CreateCell(2).SetCellValue(Convert.ToDouble(dt1["剩余租金"]));

                Double fz = Convert.ToDouble(dt1["逾期1期客户剩余租金"]) + Convert.ToDouble(dt1["逾期2期客户剩余租金"]) + Convert.ToDouble(dt1["逾期3期及以上客户剩余租金"]);
                Double fm= Convert.ToDouble(dt1["剩余租金"]);
                if (fm == 0)
                    row1.CreateCell(3).SetCellValue(0);
                else
                    row1.CreateCell(3).SetCellValue(Convert.ToDouble((fz/fm)));//逾期债权比例                
                row1.CreateCell(4).SetCellValue(Convert.ToInt32(dt1["无逾期客户数"]));
                row1.CreateCell(5).SetCellValue(Convert.ToInt32(dt1["逾期1期客户数"]));
                row1.CreateCell(6).SetCellValue(Convert.ToInt32(dt1["逾期2期客户数"]));
                row1.CreateCell(7).SetCellValue(Convert.ToInt32(dt1["逾期3期及以上客户数"]));
                //---------
                //row1.CreateCell(8).SetCellValue(AliasName);
                row1.CreateCell(8).SetCellValue(Convert.ToInt32(dt2["总客户数"]));
                row1.CreateCell(9).SetCellValue(Convert.ToDouble(dt2["剩余租金"]));
                
                fz= Convert.ToDouble(dt2["逾期1期客户剩余租金"])+Convert.ToDouble(dt2["逾期2期客户剩余租金"])+Convert.ToDouble(dt2["逾期3期及以上客户剩余租金"]);
                fm= Convert.ToDouble(dt2["剩余租金"]);
                if (fm == 0)
                    row1.CreateCell(10).SetCellValue(0);
                else
                    row1.CreateCell(10).SetCellValue(Convert.ToDouble((fz/fm)));//逾期债权比例                
                row1.CreateCell(11).SetCellValue(Convert.ToInt32(dt2["无逾期客户数"]));
                row1.CreateCell(12).SetCellValue(Convert.ToInt32(dt2["逾期1期客户数"]));
                row1.CreateCell(13).SetCellValue(Convert.ToInt32(dt2["逾期2期客户数"]));
                row1.CreateCell(14).SetCellValue(Convert.ToInt32(dt2["逾期3期及以上客户数"]));

                startRow++;
            }
        }
    }
}